package vtt.android.dapanhc.database;

import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import vtt.android.dapanhc.BaseApplication;
import vtt.android.dapanhc.entities.Answer;
import vtt.android.library.utils.VttLog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class AnswerDAO {

	private static AnswerDAO instance = null;
	
	VttLog log = new VttLog("ResultDAO", BaseApplication.isDebuggable);
	// field database
	public static String TABLE_QUESTIONNAIRE = "questionnaire";
	public static String TABLE_CHALLENGE_DATE = "challenge_date";

	// Database fields
	private SQLiteDatabase db;
	private MySQLiteOpenHelper dbHelper;
	
	public static AnswerDAO getInstance(Context context){
		if(instance == null){
			instance = new AnswerDAO(context);
		}
		return instance;
	}

	private AnswerDAO(Context context) {
		dbHelper = new MySQLiteOpenHelper(context);
	}

	public void open() throws SQLException {
		if(db == null) {
			db = dbHelper.getWritableDatabase();
		}
		
	}

	public void close() {
		dbHelper.close();
		db = null;
	}

	public List<Answer> getAnswerByDate(String strDate) {
		List<Answer> answerList = new ArrayList<Answer>();
		// Select All Query
		String selectQuery = 
				" SELECT t2.sid, suggestion, en, vn, sound_url, t1.date " +
				" FROM challenge_date t1 LEFT JOIN questionnaire t2 on t1.sid = t2.sid " +
				" WHERE date = '" + strDate + "'" ;
		//log.d("VTT>>>getAnswerByDate selectQuery " + selectQuery);
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Answer answer = new Answer();
				answer.sid = cursor.getString(0);
				answer.suggestion = cursor.getString(1);
				answer.en = cursor.getString(2);
				answer.vn = cursor.getString(3);
				answer.soundUrl = cursor.getString(4);
				answer.date = cursor.getString(5);
				
				answerList.add(answer);
			} while (cursor.moveToNext());
		}
		return answerList;
	}
	
	public List<String> getChallengeDate() {
		List<String> dateList = new ArrayList<String>();
		// Select All Query
		String selectQuery = " SELECT DISTINCT date FROM challenge_date order by date desc ";
		//log.d("VTT>>>getChallengeDate selectQuery " + selectQuery);
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				String date = cursor.getString(0);
				dateList.add(getDate(date));
			} while (cursor.moveToNext());
		}
		return dateList;
	}
	
	// save answer
	public void saveAnswer(Answer answer){
		// insert questionnaire
		/*String query = "INSERT INTO [questionnaire] ([sid], [suggestion], [en], [vn], [sound_url]) VALUES ";
		db.execSQL(query + String.format("('%s', '%s', '%s', '%s', '%s');", 
				answer.sid,answer.suggestion, answer.en, answer.vn, answer.soundUrl));
		log.d("VTT>>>saveAnswer questionnaire query " + query);*/
		
		ContentValues answerParams = new ContentValues();
	    answerParams.put("sid", answer.sid);
	    answerParams.put("suggestion", answer.suggestion);
	    answerParams.put("en", answer.en);
	    answerParams.put("vn", answer.vn);
	    answerParams.put("sound_url", answer.soundUrl);
		db.insertWithOnConflict(TABLE_QUESTIONNAIRE, null, answerParams, SQLiteDatabase.CONFLICT_IGNORE);
		
		ContentValues challengeDateParams = new ContentValues();
		challengeDateParams.put("date", answer.date);
		challengeDateParams.put("sid", answer.sid);
		db.insertWithOnConflict(TABLE_CHALLENGE_DATE, null, challengeDateParams, SQLiteDatabase.CONFLICT_IGNORE);
		
		// insert challenge date
		/*String query2 = "INSERT INTO [challenge_date] ([date], [sid]) VALUES ";
		db.execSQL(query2 + String.format("('%s', '%s');", answer.date,answer.sid));
		log.d("VTT>>>saveAnswer challenge_date query " + query2);*/
		
	}
	
	public boolean checkNewDate(String date){
		String selectQuery = " SELECT date FROM challenge_date where date ='" + date + "' LIMIT 0,1";
		//log.d("VTT>>> checkNewDate selectQuery " + selectQuery);
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.getCount() > 0) {
			return false;
		}
		return true;
	}
	
	private String getDate(String date){
		String year = date.substring(0, 4);
		String month = date.substring(4, 6);
		String day = date.substring(6, 8);
		return day + "/" + month + "/" + year;
	}

	public JSONArray loadAnswerByDate(String selectedDate) throws JSONException {
		JSONArray answerList = new JSONArray();
		// Select All Query
		String selectQuery = 
				" SELECT t2.sid, suggestion, en, vn, sound_url, t1.date " +
				" FROM challenge_date t1 LEFT JOIN questionnaire t2 on t1.sid = t2.sid " +
				" WHERE date = '" + selectedDate + "'" ;
		log.d("VTT>>>getAnswerByDate selectQuery " + selectQuery);
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				JSONObject answer = new JSONObject();
				answer.put("sid", cursor.getString(0));
				answer.put("suggestion", cursor.getString(1));
				answer.put("en", cursor.getString(2));
				answer.put("vn", cursor.getString(3));
				answer.put("soundUrl", cursor.getString(4));
				answer.put("date", cursor.getString(5));
				
				answerList.put(answer);
			} while (cursor.moveToNext());
		}
		return answerList;
	}
}
